package model;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import com.mysql.cj.jdbc.result.ResultSetMetaData;
/*
 * 
 */
public class readExcelFromDataBase {

	private static String url = "jdbc:mysql://localhost:3306/javatest?useSSL=false&useUnicode=true&characterEncoding=utf-8";
	private static String user = "root";
	private static String password = "123";

	public static void readExcel(String tablename, String outname) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 连接数据库查询数据
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection(url, user, password);
			String sql = "select * from " + tablename;
			ps = conn.prepareStatement(sql);
			// 获取结果集
			rs = ps.executeQuery();
			// 用于获取字段的描述信息,比如字段名
			ResultSetMetaData metaData = (ResultSetMetaData) rs.getMetaData();
			// 创建workBook对象
			@SuppressWarnings("resource")
			HSSFWorkbook workBook = new HSSFWorkbook();
			// 在workBook对象中创建一张表格
			HSSFSheet sheet = workBook.createSheet("学生成绩");
			// 设置每一列的宽度
			int colnum = metaData.getColumnCount();
			for (int i = 0; i < colnum; i++) {
				sheet.setColumnWidth(i, 2000);
			}
			// 单元格样式对象
			HSSFCellStyle cellStyle = workBook.createCellStyle();
			// 设置文本居中
			cellStyle.setAlignment(HorizontalAlignment.CENTER);
			// 创建第0行,作为表格的表头
			HSSFRow row = sheet.createRow(0);
			HSSFCell cell = null;
			for (int i = 0; i < colnum; i++) {
				cell = row.createCell(i);
				// 动态获取字段名
				cell.setCellValue(metaData.getColumnLabel(i + 1));
				cell.setCellStyle(cellStyle);
			}
			int rowIndex = 1;
			while (rs.next()) {
				// 循环将查询出来的数据封装到表格的一行中
				row = sheet.createRow(rowIndex);
				for (int i = 0; i < colnum; i++) {
					cell = row.createCell(i);
					cell.setCellValue(rs.getString(i + 1));
					cell.setCellStyle(cellStyle);
				}
				rowIndex++;
			}
			String outPutFile = "d:" + File.separator + outname + ".xls";
			FileOutputStream fos = new FileOutputStream(outPutFile);
			// 输出流将文件写到硬盘
			workBook.write(fos);
			fos.flush();
			fos.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			rs.close();
			ps.close();
			conn.close();
		}
	}

}
